package com.sql;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.manager.People;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class UserBDD {

	private SQLiteDatabase _db;
	private BDDHelper _dbhelper;
	private String _tableUser = "TableUser";
	private String _tableTel = "TableTel";
	private String _tableMail = "TableMail";
	
	public UserBDD(Context context)
	{
		_dbhelper = new BDDHelper(context, "contact.db", null, 1);
	}
	
	public void open()
	{
		_db = _dbhelper.getWritableDatabase();
	}
	
	public void close()
	{
		_db.close();
	}
	
	public SQLiteDatabase getdatabase()
	{
		return this._db;
	}
	
	public int insertUser(People obj)
	{
		int res = 0;
		
		ContentValues values = new ContentValues();
		
		values.put("Name", obj.get_name());
		values.put("Surname", obj.get_surname());
		values.put("Pseudo", obj.get_pseudo());
		values.put("Address", obj.getAddress());
		res = (int) _db.insert(_tableUser, null, values);
		this.insertPhonesUser(res, obj.get_phones());
		this.insertMailsUser(res, obj.get_mails());
				
		return res;
	}
	
	public long insertPhonesUser(int id, List<String> phones)
	{
		long res = 0;

		
		for(Iterator<String> it = phones.iterator(); it.hasNext();)
		{
			ContentValues values = new ContentValues();			
			String element = it.next();
			
			values.put("ID_User", id);
			values.put("Tel", element);
			
			res = res + _db.insert(_tableTel, null, values);
		}
		
		return res;
	}
	
	public long insertMailsUser(int id, List<String> mails)
	{
		long res = 0;
		
		for(Iterator<String> it = mails.iterator(); it.hasNext();)
		{
			ContentValues values = new ContentValues();
			String element = it.next();
			
			values.put("ID_User", id);
			values.put("Mail", element);
			
			res = res + _db.insert(_tableMail, null, values);
		}
		
		return res;
	}
	
	public int updateUser(People obj)
	{
		ContentValues values = new ContentValues();		
		values.put("Name", obj.get_name());
		values.put("Surname", obj.get_surname());
		values.put("Pseudo", obj.get_surname());
		values.put("Address", obj.getAddress());
		
		return _db.update(_tableUser, values, "ID = " + obj.get_id(), null);
	}
	
	public int updatePhone(int id, String value)
	{
		ContentValues values = new ContentValues();		
		values.put("Tel", value);		
		
		return _db.update(_tableUser, values,"ID_User = " + id + " AND Tel = " + value, null);
	}
	
	public int updateMail(int id, String value)
	{
		ContentValues values = new ContentValues();
		values.put("Mail", value);	
		
		return _db.update(_tableUser, values, "ID_User = " + id + " AND Mail = " + value, null);
	}
	
	public int removeUser(int id)
	{
		return _db.delete(_tableUser, "ID = " + id, null);
	}
	
	public int removePhone(int id, String value)
	{
		return _db.delete(_tableUser, "ID_User = " + id + " AND Tel = " + value, null);
	}
	
	public int removeMail(int id, String value)
	{
		return _db.delete(_tableUser, "ID_User = " + id + " AND Mail = " + value, null);
	}
	
	public People getUserbyID(int id)
	{
		Cursor c = _db.query(_tableUser, new String[] {"ID", "Name", "Surname", "Pseudo", "Address", "Notes"}, "ID = " + id, null, null, null, null);		
		
		People result = new People();			
		
		c.moveToFirst();
		result.set_id(c.getInt(0));
		result.set_name(c.getString(1));
		result.set_surname(c.getString(2));
		result.set_pseudo(c.getString(3));
		result.setAddress(c.getString(4));
		result.setNotes(c.getString(5));
		result.set_phones(this.getPhonesUserByID(id));
		result.set_mails(this.getMailsUserByID(id));
		
		c.close();
		
		return result;
	}
	
	public List<String> getPhonesUserByID(int id)
	{
		List<String> phones = new ArrayList<String>();
		Cursor c = _db.query(_tableTel, new String[] {"ID", "Tel", "ID_User"}, "ID_User = " + id, null, null, null, null);
		
		if (c.getCount() > 0)
		{		
			c.moveToFirst();
			
			for (int i = 0; i < c.getCount() ; ++i)
			{
				phones.add(c.getString(1));
				c.moveToNext();
			}

			c.close();
		}
		
		return phones;
	}
	
	public List<String> getMailsUserByID(int id)
	{
		List<String> mails = new ArrayList<String>();
		Cursor c = _db.query(_tableMail, new String[] {"ID_User", "Mail"}, "ID_User = " + id, null, null, null, null);
		
		if (c.getCount() > 0)
		{		
			c.moveToFirst();
			
			for (int i = 0; i < c.getCount() ; ++i)
			{
				mails.add(c.getString(1));
				c.moveToNext();
			}
			
			c.close();
		}
		
		return mails;
	}
	
	public List<People> getUsers()
	{
		List<People> listpeople = new ArrayList<People>();
		
		
		Cursor c = _db.query(_tableUser, new String[] {"ID", "Name", "Surname", "Pseudo", "Address", "Notes"}, null, null, null, null, "ID ASC");
		
		c.moveToFirst();
		
		for (int i = 0; i < c.getCount() ; ++i)
		{
			People result = new People();
			
			result.set_id(c.getInt(0));			
			result.set_name(c.getString(1));
			result.set_surname(c.getString(2));
			result.set_pseudo(c.getString(3));
			result.setAddress(c.getString(4));
			result.setNotes(c.getString(5));
			result.set_phones(this.getPhonesUserByID(result.get_id()));
			result.set_mails(this.getMailsUserByID(result.get_id()));
			
			listpeople.add(result);
			result = null;
			
			c.moveToNext();
		}
		
		c.close();
		
		return listpeople;
	}
}
